/*
mssql_schema.sql for R3-born
 */

BEGIN TRANSACTION
GO

CREATE TABLE [r3born_banlist] (
	[ban_id] [int] IDENTITY (1, 1) NOT NULL ,
	[ban_userid] [int] NULL ,
	[ban_ip] [char] (8) NULL ,
	[ban_email] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_config] (
	[config_name] [varchar] (255) DEFAULT ('') NOT NULL ,
	[config_value] [varchar] (255) DEFAULT ('') NOT NULL ,
	[is_dynamic] [int] DEFAULT (0) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [r3born_config] WITH NOCHECK ADD 
	CONSTRAINT [PK_r3born_config] PRIMARY KEY  CLUSTERED 
	(
		[config_name]
	)  ON [PRIMARY] 
GO

CREATE  INDEX [is_dynamic] ON [r3born_config]([is_dynamic]) ON [PRIMARY]
GO

CREATE TABLE [r3born_confirm] (
	[confirm_id] [char] (32) NOT NULL ,
	[session_id] [char] (32) NOT NULL ,
	[code] [char] (6) NOT NULL ,
	[confirm_type] [tinyint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [r3born_confirm] WITH NOCHECK ADD
	CONSTRAINT [DF_r3born_confirm_confirm_type] DEFAULT (0) FOR [confirm_type]
GO


CREATE TABLE [r3born_disallow] (
	[disallow_id] [int] IDENTITY (1, 1) NOT NULL ,
	[disallow_username] [varchar] (100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_groups] (
	[group_id] [int] IDENTITY (1, 1) NOT NULL ,
	[group_type] [smallint] NULL ,
	[group_name] [varchar] (50) NOT NULL ,
	[group_description] [varchar] (255) NOT NULL ,
	[group_moderator] [int] NULL ,
	[group_single_user] [smallint] NOT NULL ,
    [group_colored] [smallint] NOT NULL ,
    [group_colors] [text] NOT NULL ,
    [group_order] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_search_results] (
	[search_id] [int] NOT NULL ,
	[session_id] [char] (32) NOT NULL ,
	[search_time] [int] NOT NULL ,
	[search_array] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [r3born_search_results] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_search_results] PRIMARY KEY  CLUSTERED
	(
		[search_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_search_results] WITH NOCHECK ADD
	CONSTRAINT [DF_r3born_search_results_search_time] DEFAULT (0) FOR [search_time]
GO

CREATE  INDEX [IX_r3born_search_results] ON [r3born_search_results]([session_id]) ON [PRIMARY]
GO

CREATE TABLE [r3born_search_wordlist] (
	[word_id] [int] IDENTITY (1, 1) NOT NULL ,
	[word_text] [varchar] (50) NOT NULL ,
	[word_common] [tinyint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [r3born_search_wordlist] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_search_wordlist] PRIMARY KEY  CLUSTERED
	(
		[word_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_search_wordlist] WITH NOCHECK ADD
	CONSTRAINT [DF_r3born_search_wordlist_word_common] DEFAULT (0) FOR [word_common]
GO

CREATE UNIQUE INDEX [IX_r3born_search_wordlist] ON [r3born_search_wordlist]([word_text]) WITH  IGNORE_DUP_KEY  ON [PRIMARY]
GO

CREATE INDEX [IX_r3born_search_wordlist_1] ON [r3born_search_wordlist]([word_common]) ON [PRIMARY]
GO

CREATE TABLE [r3born_search_wordmatch] (
	[parent_id] [int] NOT NULL ,
	[parent_field] [varchar] (32) NOT NULL DEFAULT (''),	
	[module_name] [varchar] (64) NOT NULL DEFAULT (''),
	[word_id] [int] NOT NULL ,
	[title_match] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_r3born_search_wordmatch] ON [r3born_search_wordmatch]([parent_id]) ON [PRIMARY]
GO

CREATE INDEX [IX_r3born_search_wordmatch_1] ON [r3born_search_wordmatch]([word_id]) ON [PRIMARY]
GO

CREATE INDEX [IX_r3born_search_wordmatch_2] ON [r3born_search_wordmatch]([module_name]) ON [PRIMARY];
GO

CREATE TABLE r3born_search_rebuild (
    rebuild_session_id INTEGER NOT NULL IDENTITY(1, 1),
    start_post_id INTEGER NOT NULL DEFAULT (0),
    end_post_id INTEGER NOT NULL DEFAULT (0),
    module_name VARCHAR(64) NOT NULL DEFAULT (''),
    start_time INTEGER NOT NULL DEFAULT (0),
    end_time INTEGER NOT NULL DEFAULT (0),
    last_cycle_time INTEGER NOT NULL DEFAULT (0),
    session_time INTEGER NOT NULL DEFAULT (0),
    session_posts INTEGER NOT NULL DEFAULT (0),
    session_cycles INTEGER NOT NULL DEFAULT (0),
    search_size INTEGER NOT NULL DEFAULT (0),
    rebuild_session_status SMALLINT NOT NULL DEFAULT (0),
    CONSTRAINT r3born_search_rebuild_pk PRIMARY KEY (rebuild_session_id) ON [PRIMARY],
    CHECK (rebuild_session_id>=0),
    CHECK (start_post_id>=0),
    CHECK (end_post_id>=0),
    CHECK (session_posts>=0),
    CHECK (session_cycles>=0),
    CHECK (search_size>=0)
)  ON [PRIMARY];
GO

CREATE INDEX r3born_search_rebuildendpostid ON r3born_search_rebuild (end_post_id) ON [PRIMARY];
GO

CREATE TABLE [r3born_sessions] (
	[session_id] [char] (32) NOT NULL ,
	[session_user_id] [int] NOT NULL ,
	[session_start] [int] NULL ,
	[session_time] [int] NULL ,
	[session_ip] [char] (8) NOT NULL ,
	[session_browser] [char] (150) NOT NULL ,	
	[session_page] [int] NULL ,
	[session_logged_in] [smallint] NULL,
	[session_admin] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_sessions_keys] (
  [key_id] [char] (32) NOT NULL ,
  [user_id] [int] NOT NULL ,
  [last_ip] [char] (8) NOT NULL ,
  [last_login] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_smilies] (
	[smilies_id] [int] IDENTITY (1, 1) NOT NULL ,
	[code] [varchar] (10) NOT NULL ,
	[smile_url] [varchar] (50) NOT NULL ,
	[emoticon] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_themes] (
	[themes_id] [int] IDENTITY (1, 1) NOT NULL ,
	[template_name] [varchar] (30) NOT NULL ,
	[style_name] [varchar] (50) NOT NULL ,
	[head_stylesheet] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [r3born_user_group] (
	[group_id] [int] NOT NULL ,
	[user_id] [int] NOT NULL ,
	[user_pending] [smallint] NULL ,
	[group_moderator] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_user_group_auth_access] (
	[group_id] [int] NULL ,
	[auth_mod] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [r3born_users] (
	[user_id] [int] NOT NULL ,
	[user_active] [smallint] NULL ,
	[username] [varchar] (25) NOT NULL ,
	[user_password] [varchar] (32) NOT NULL ,
	[user_session_time] [int] NOT NULL ,
	[user_session_page] [smallint] NOT NULL ,
	[user_lastvisit] [int] NOT NULL ,
	[user_regdate] [int] NOT NULL ,
	[user_level] [smallint] NOT NULL ,
	[user_timezone] [decimal] (5,2) NOT NULL ,
	[user_style] [int] NULL ,
	[user_lang] [varchar] (255) NULL ,
	[user_dateformat] [varchar] (20) NOT NULL ,
	[user_login_tries] [smallint] NOT NULL ,
	[user_last_login_try] [int] NOT NULL ,
	[user_emailtime] [int] NOT NULL ,
	[user_viewemail] [smallint] NULL ,
	[user_attachsig] [smallint] NULL ,
	[user_allowhtml] [smallint] NULL ,
	[user_allowbbcode] [smallint] NULL ,
	[user_allowsmile] [smallint] NULL ,
	[user_allowavatar] [smallint] NULL ,
	[user_allow_viewonline] [smallint] NOT NULL ,
	[user_avatar_type] [smallint] NULL ,
	[user_avatar] [varchar] (100) NULL ,
	[user_email] [varchar] (255) NULL ,
	[user_icq] [varchar] (15) NULL ,
	[user_website] [varchar] (100) NULL ,
	[user_occ] [varchar] (100) NULL ,
	[user_from] [varchar] (100) NULL ,
	[user_sig] [text] NULL ,
	[user_sig_bbcode_uid] [char] (10) NULL ,
	[user_aim] [varchar] (255) NULL ,
	[user_yim] [varchar] (255) NULL ,
	[user_msnm] [varchar] (255) NULL ,
	[user_interests] [varchar] (255) NULL ,
	[user_actkey] [varchar] (32) NULL ,
	[user_newpasswd] [varchar] (32) NULL ,
	[user_dst] [smallint] NOT NULL ,
    [group_priority] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [r3born_words] (
	[word_id] [int] IDENTITY (1, 1) NOT NULL ,
	[word] [varchar] (255) NOT NULL ,
	[replacement] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [r3born_banlist] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_banlist] PRIMARY KEY  CLUSTERED
	(
		[ban_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_confirm] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_confirm] PRIMARY KEY  CLUSTERED
	(
		[session_id],[confirm_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_disallow] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_disallow] PRIMARY KEY  CLUSTERED
	(
		[disallow_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_groups] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_groups] PRIMARY KEY  CLUSTERED
	(
		[group_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_smilies] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_smilies] PRIMARY KEY  CLUSTERED
	(
		[smilies_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_themes] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_themes] PRIMARY KEY  CLUSTERED
	(
		[themes_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_themes_name] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_themes_name] PRIMARY KEY  CLUSTERED
	(
		[themes_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_users] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_users] PRIMARY KEY  CLUSTERED
	(
		[user_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_words] WITH NOCHECK ADD
	CONSTRAINT [PK_r3born_words] PRIMARY KEY  CLUSTERED
	(
		[word_id]
	)  ON [PRIMARY]
GO

ALTER TABLE [r3born_user_group_auth_access] WITH NOCHECK ADD
	CONSTRAINT [DF_r3born_user_group_auth_access_auth_mod] DEFAULT (0) FOR [auth_mod]
GO

ALTER TABLE [r3born_confirm] WITH NOCHECK ADD
	CONSTRAINT [DF_r3born_confirm_confirm_id] DEFAULT ('') FOR [confirm_id],
	CONSTRAINT [DF_r3born_confirm_session_id] DEFAULT ('') FOR [session_id],
	CONSTRAINT [DF_r3born_confirm_code] DEFAULT ('') FOR [code]
GO

ALTER TABLE [r3born_users] WITH NOCHECK ADD
	CONSTRAINT [DF_r3born_users_user_level] DEFAULT (0) FOR [user_level],
	CONSTRAINT [DF_r3born_users_user_session_time] DEFAULT (0) FOR [user_session_time],
	CONSTRAINT [DF_r3born_users_user_session_page] DEFAULT (0) FOR [user_session_page],
	CONSTRAINT [DF_r3born_users_user_lastvisit] DEFAULT (0) FOR [user_lastvisit],
	CONSTRAINT [DF_r3born_users_user_login_tries] DEFAULT (0) FOR [user_login_tries],
	CONSTRAINT [DF_r3born_users_user_last_login_try] DEFAULT (0) FOR [user_last_login_try],
	CONSTRAINT [DF_r3born_users_user_emailtime] DEFAULT (0) FOR [user_emailtime],
	CONSTRAINT [DF_r3born_users_user_viewemail] DEFAULT (1) FOR [user_viewemail],
	CONSTRAINT [DF_r3born_users_user_attachsig] DEFAULT (1) FOR [user_attachsig],
	CONSTRAINT [DF_r3born_users_user_allowhtml] DEFAULT (0) FOR [user_allowhtml],
	CONSTRAINT [DF_r3born_users_user_allowbbcode] DEFAULT (1) FOR [user_allowbbcode],
	CONSTRAINT [DF_r3born_users_user_allowsmile] DEFAULT (1) FOR [user_allowsmile],
	CONSTRAINT [DF_r3born_users_user_allowavatar] DEFAULT (1) FOR [user_allowavatar],
	CONSTRAINT [DF_r3born_users_user_allow_viewonline] DEFAULT (1) FOR [user_allow_viewonline],
	CONSTRAINT [DF_r3born_users_user_dateformat] DEFAULT('d M Y H:i') FOR [user_dateformat],
	CONSTRAINT [DF_r3born_users_user_avatar_type] DEFAULT (0) FOR [user_avatar_type],
	CONSTRAINT [DF_r3born_users_user_dst] DEFAULT (0) FOR [user_dst]
GO

 CREATE  INDEX [IX_r3born_user_group_auth_access] ON [r3born_user_group_auth_access]([group_id]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_banlist] ON [r3born_banlist]([ban_userid], [ban_ip]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_groups] ON [r3born_groups]([group_single_user]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_sessions] ON [r3born_sessions]([session_id], [session_user_id], [session_ip], [session_logged_in]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_sessions_keys] ON [r3born_sessions_keys]([key_id], [user_id]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_sessions_keys_1] ON [r3born_sessions_keys]([last_login]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_users] ON [r3born_users]([user_session_time]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_r3born_user_group] ON [r3born_user_group]([group_id], [user_id]) ON [PRIMARY]
GO

/*
 R3-born MS-SQL DB schema
*/

CREATE TABLE r3born_modules (
    module_id INTEGER NOT NULL IDENTITY(1, 1),
    module_name VARCHAR(64) NOT NULL DEFAULT (''),
    module_type INTEGER NOT NULL DEFAULT (0),
    module_displayname VARCHAR(64) NOT NULL DEFAULT (''),
    module_description VARCHAR(255) NOT NULL DEFAULT (''),
    module_version VARCHAR(10) NOT NULL DEFAULT (''),
	module_version_check VARCHAR(255) NOT NULL DEFAULT (''),
    module_copyright TEXT NOT NULL DEFAULT (''),
    module_files TEXT NULL,
    module_templates TEXT NULL,
    module_admin TEXT NULL,
    module_language TEXT NULL,
    module_constants TEXT NULL,
    module_headers TEXT NULL,
    module_components TEXT NULL,	
    module_state SMALLINT NOT NULL DEFAULT (0),
    CONSTRAINT r3born_modules_pk PRIMARY KEY (module_id) ON [PRIMARY],
    CHECK (module_id>=0)
)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

CREATE TABLE r3born_jr_admin_users (
    user_id INTEGER NOT NULL DEFAULT (0),
    user_jr_admin LONGTEXT NOT NULL,
    start_date INTEGER NOT NULL DEFAULT (0),
    update_date INTEGER NOT NULL DEFAULT (0),
    admin_notes TEXT NOT NULL,
    notes_view SMALLINT NOT NULL DEFAULT (0),
    CONSTRAINT r3born_jr_admin_users_pk PRIMARY KEY (user_id) ON [PRIMARY],
    CHECK (start_date>=0),
    CHECK (update_date>=0)
)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

CREATE TABLE r3born_policy (
    policy_id INTEGER NOT NULL DEFAULT (0),
    policy_name VARCHAR(40) NOT NULL,
    policy_text TEXT NOT NULL,
    policy_date INTEGER NOT NULL DEFAULT (0),
    CONSTRAINT r3born_policy_pk PRIMARY KEY (policy_id) ON [PRIMARY]
)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

CREATE TABLE r3born_admin_nav (
    user_id INTEGER NOT NULL DEFAULT (0),
    modulname VARCHAR(200) NOT NULL DEFAULT ('')
)  ON [PRIMARY];
GO

CREATE TABLE r3born_layout (
    layout_id INTEGER NOT NULL IDENTITY(1, 1),
    layout_url VARCHAR(100) NOT NULL DEFAULT (''),	
    layout_view SMALLINT NOT NULL DEFAULT (0),
    groups TEXT NOT NULL,
    module_id INTEGER NOT NULL,    
    CONSTRAINT r3born_layout_pk PRIMARY KEY (layout_id) ON [PRIMARY],
    CHECK (layout_id>=0)
)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

CREATE INDEX r3born_layout_module_id ON r3born_layout (module_id) ON [PRIMARY];
GO

CREATE TABLE r3born_blocks (
    block_id INTEGER NOT NULL IDENTITY(1, 1),
    block_position INTEGER NOT NULL DEFAULT (0),
    block_weight INTEGER NOT NULL DEFAULT (1),
    block_view SMALLINT NOT NULL DEFAULT (0),
    groups TEXT NOT NULL,
    module_id INTEGER NOT NULL,
    layout_id INTEGER NOT NULL DEFAULT (1),
    CONSTRAINT r3born_blocks_pk PRIMARY KEY (block_id) ON [PRIMARY],
    CHECK (block_id>=0),
    CHECK (module_id>=0),
    CHECK (layout_id>=0)
)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

CREATE INDEX r3born_blocks_module_id ON r3born_blocks (module_id) ON [PRIMARY];

CREATE INDEX r3born_blocks_layout_id ON r3born_blocks (layout_id) ON [PRIMARY];
GO

COMMIT
GO
